﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using DevExpress.Utils.Menu;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid.Columns;
using DevExpress.XtraGrid.Views.Grid;
using ERP_RORZE_SYSTEM;
using ERP_RORZE_TOOL.Common;

namespace ERP_RORZE_TOOL.Tool.BCThoigianlamviecthang
{
    public partial class ucBCTimeMonth : DevExpress.XtraEditors.XtraUserControl
    {
        DXPopupMenu menu;
        private int ReportId;

        public ucBCTimeMonth()
        {
            InitializeComponent();
            InitPopupMenu();
            BindDataReportList();
            BindDataItemList();
            this.dropbtExport.DropDownControl = menu;
            lblFormName.Text = ReportConstants.FormName;
        }

        private void cmbReportList_SelectionChangeCommitted(object sender, EventArgs e)
        {
            ReportConstants.Item item = (ReportConstants.Item)cmbReportList.SelectedItem;
            cmbData.SelectedItem = cmbData.Items[0];
            if (item.Value != -1)
            {
                panelBottom.Visible = false;
                this.ReportId = item.Value;
                BindDataToGridview(item.Value);
                
            }
        }

        private void cmbData_SelectionChangeCommitted(object sender, EventArgs e)
        {
            ReportConstants.Item item = (ReportConstants.Item)cmbData.SelectedItem;
            cmbReportList.SelectedItem = cmbReportList.Items[0];
            if (item.Value != -1)
            {

                gridView1.Columns.Clear();
                panelBottom.Visible = true;
                this.ReportId = item.Value;
                BindDataToGridview(item.Value);
            }
        }

        private void BindDataReportList()
        {
            cmbReportList.Items.Add(new ReportConstants.Item("Select..."));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.CheckThoiGian, ReportConstants.ReportId.CheckThoiGian));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.WorkingTime, ReportConstants.ReportId.WorkingTime));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.Data, ReportConstants.ReportId.Data));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.Data2, ReportConstants.ReportId.Data2));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.WTSTOGA, ReportConstants.ReportId.WTSTOGA));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.Baocao, ReportConstants.ReportId.Baocao));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.HangMuaNgoaiF2Gt, ReportConstants.ReportId.HangMuaNgoaiF2Gt));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.SsMaCvCacBoPhan, ReportConstants.ReportId.SsMaCvCacBoPhan));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.SsMaCvXuong, ReportConstants.ReportId.SsMaCvXuong));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.TgTongHop, ReportConstants.ReportId.TgTongHop));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.TgMcVktraMa, ReportConstants.ReportId.TgMcVktraMa));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.TGMCVXuong, ReportConstants.ReportId.TGMCVXuong));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.TGTonghopkiemtra, ReportConstants.ReportId.TGTonghopkiemtra));
            cmbReportList.Items.Add(new ReportConstants.Item(ReportConstants.ReportName.TGChitietKiemtra, ReportConstants.ReportId.TGChitietKiemtra));

            cmbReportList.SelectedItem = cmbReportList.Items[0];

            cmbReportList.DropDown += BCThoigianlamviecthang.ReportConstants.AdjustWidthComboBox;
        }

        private void BindDataToGridview(int reportId)
        {
            DataTable dt = new DataTable();
            gridView1.Columns.Clear();
            switch (reportId)
            {
                case ReportConstants.ReportId.CheckThoiGian:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.CheckThoiGian);
                    gridView1.ViewCaption = ReportConstants.ReportName.CheckThoiGian;
                    break;
                case ReportConstants.ReportId.WorkingTime:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.WorkingTime);
                    gridView1.ViewCaption = ReportConstants.ReportName.WorkingTime;
                    break;
                case ReportConstants.ReportId.Data:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.Data);
                    gridView1.ViewCaption = ReportConstants.ReportName.Data;
                    break;
                case ReportConstants.ReportId.Data2:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.Data2);
                    gridView1.ViewCaption = ReportConstants.ReportName.Data2;
                    break;
                case ReportConstants.ReportId.WTSTOGA:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.WTSTOGA);
                    gridView1.ViewCaption = ReportConstants.ReportName.WTSTOGA;
                    break;
                case ReportConstants.ReportId.Baocao:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.Baocao);
                    gridView1.ViewCaption = ReportConstants.ReportName.Baocao;
                    break;
                case ReportConstants.ReportId.HangMuaNgoaiF2Gt:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.HangMuaNgoaiF2Gt);
                    gridView1.ViewCaption = ReportConstants.ReportName.HangMuaNgoaiF2Gt;
                    break;
                case ReportConstants.ReportId.SsMaCvCacBoPhan:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.SsMaCvCacBoPhan);
                    gridView1.ViewCaption = ReportConstants.ReportName.SsMaCvCacBoPhan;
                    break;
                case ReportConstants.ReportId.SsMaCvXuong:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.SsMaCvXuong);
                    gridView1.ViewCaption = ReportConstants.ReportName.SsMaCvXuong;
                    break;
                case ReportConstants.ReportId.TgTongHop:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.TgTongHop);
                    gridView1.ViewCaption = ReportConstants.ReportName.TgTongHop;
                    break;
                case ReportConstants.ReportId.TgMcVktraMa:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.TgMcVktraMa);
                    gridView1.ViewCaption = ReportConstants.ReportName.TgMcVktraMa;
                    break;
                case ReportConstants.ReportId.TGMCVXuong:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.TGMCVXuong);
                    gridView1.ViewCaption = ReportConstants.ReportName.TGMCVXuong;
                    break;
                case ReportConstants.ReportId.TGTonghopkiemtra:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.TGTonghopkiemtra);
                    gridView1.ViewCaption = ReportConstants.ReportName.TGTonghopkiemtra;
                    break;
                case ReportConstants.ReportId.TGChitietKiemtra:
                    gridView1.OptionsBehavior.Editable = false;
                    dt = ReportDAO.ExcuteProcedure(ReportConstants.StoredProcedure.TGChitietKiemtra);
                    gridView1.ViewCaption = ReportConstants.ReportName.TGChitietKiemtra;
                    break;

                    //data
                case ReportConstants.ReportId.Timecard:
                    gridView1.OptionsBehavior.Editable = true;
                    GridColumn colIdShift = new GridColumn()
                        {
                            Caption = String.Format("ID"),
                            FieldName = "ID",
                        };
                    gridView1.Columns.Add(colIdShift);
                    colIdShift.VisibleIndex = -1;

                    GridColumn colDate = new GridColumn()
                        {
                            Caption = String.Format("Date"),
                            FieldName = "Date",
                        };
                    gridView1.Columns.Add(colDate);
                    colDate.VisibleIndex = 0;

                    GridColumn colStaffID = new GridColumn()
                        {
                            Caption = String.Format("StaffID"),
                            FieldName = "StaffID",
                        };
                    gridView1.Columns.Add(colStaffID);
                    colStaffID.VisibleIndex = 1;

                    GridColumn colIn = new GridColumn()
                        {
                            Caption = String.Format("In"),
                            FieldName = "In",
                        };
                    gridView1.Columns.Add(colIn);
                    colIn.VisibleIndex = 2;

                    GridColumn colOut = new GridColumn()
                        {
                            Caption = String.Format("Out"),
                            FieldName = "Out",
                        };
                    gridView1.Columns.Add(colOut);
                    colOut.VisibleIndex = 3;

                    GridColumn colNote = new GridColumn()
                        {
                            Caption = String.Format("Note"),
                            FieldName = "Note",
                        };
                    gridView1.Columns.Add(colNote);
                    colNote.VisibleIndex = 4;

                    GridColumn colTime = new GridColumn()
                        {
                            Caption = String.Format("Time"),
                            FieldName = "Time",
                        };
                    gridView1.Columns.Add(colTime);
                    colTime.VisibleIndex = 5;

                    GridColumn colShift = new GridColumn()
                        {
                            Caption = String.Format("Shift"),
                            FieldName = "Shift",
                        };
                    gridView1.Columns.Add(colShift);
                    colShift.VisibleIndex = 6;

                    GridColumn colOvertime = new GridColumn()
                        {
                            Caption = String.Format("Overtime"),
                            FieldName = "Overtime",
                        };
                    gridView1.Columns.Add(colOvertime);
                    colOvertime.VisibleIndex = 7;

                    GridColumn colAbs = new GridColumn()
                        {
                            Caption = String.Format("ABS"),
                            FieldName = "ABS",
                        };
                    gridView1.Columns.Add(colAbs);
                    colAbs.VisibleIndex = 8;

                    dt = ReportDAO.ExecuteQuery("SELECT * FROM [242_TimeCard]");
                    gridView1.ViewCaption = ReportConstants.ReportName.TimeCard;
                    break;
                case ReportConstants.ReportId.StaffList:
                    gridView1.OptionsBehavior.Editable = true;
                    GridColumn colIdStaff = new GridColumn()
                        {
                            Caption = String.Format("ID"),
                            FieldName = "ID",
                        };
                    gridView1.Columns.Add(colIdStaff);
                    colIdStaff.VisibleIndex = -1;

                    GridColumn colStaffId = new GridColumn()
                        {
                            Caption = String.Format("StaffID"),
                            FieldName = "StaffID",
                        };
                    gridView1.Columns.Add(colStaffId);
                    colStaffId.VisibleIndex = 0;

                    GridColumn colStaffName = new GridColumn()
                        {
                            Caption = String.Format("StaffName"),
                            FieldName = "StaffName",
                        };
                    gridView1.Columns.Add(colStaffName);
                    colStaffName.VisibleIndex = 1;

                    GridColumn colDescription = new GridColumn()
                        {
                            Caption = String.Format("Description"),
                            FieldName = "Description",
                        };
                    gridView1.Columns.Add(colDescription);
                    colDescription.VisibleIndex = 2;

                    GridColumn colGroup1 = new GridColumn()
                        {
                            Caption = String.Format("Group1"),
                            FieldName = "Group1",
                        };
                    gridView1.Columns.Add(colGroup1);
                    colGroup1.VisibleIndex = 3;

                    GridColumn colGroup2 = new GridColumn()
                        {
                            Caption = String.Format("Group2"),
                            FieldName = "Group2",
                        };
                    gridView1.Columns.Add(colGroup2);
                    colGroup2.VisibleIndex = 4;

                    GridColumn colDept = new GridColumn()
                        {
                            Caption = String.Format("Dept"),
                            FieldName = "Dept",
                        };
                    gridView1.Columns.Add(colDept);
                    colDept.VisibleIndex = 5;

                    dt = ReportDAO.ExecuteQuery("SELECT * FROM [242_StaffList]");
                    gridView1.ViewCaption = ReportConstants.ReportName.StaffLisst;
                    break;

                default:
                    break;
            }

            gridControl1.DataSource = dt;
            //gridView1.PopulateColumns();
            gridView1.BestFitColumns();
        }

        private void BindDataItemList()
        {
            cmbData.Items.Add(new ReportConstants.Item("Select..."));
            cmbData.Items.Add(new ReportConstants.Item(ReportConstants.Table.Timecard, ReportConstants.ReportId.Timecard));
            cmbData.Items.Add(new ReportConstants.Item(ReportConstants.Table.StaffList, ReportConstants.ReportId.StaffList));

            cmbData.SelectedItem = cmbData.Items[0];

        }
        //Export to excel button

        protected virtual void InitPopupMenu()
        {
            menu = new DXPopupMenu();
            menu.Items.Add(new DXMenuItem("Export all", new EventHandler(OnItemClickExportAll), null, null, null, null));
            menu.Items.Add(new DXMenuItem("Export selected row(s)", new EventHandler(OnItemClickExportRows), null, null, null, null));

        }
        void OnItemClickExportAll(object sender, EventArgs e)
        {
            Utilities.ExportToExcel(gridView1, 1, true);
        }
        void OnItemClickExportRows(object sender, EventArgs e)
        {
            Utilities.ExportToExcel(gridView1, 0, true);
        }

        private void gridView1_CustomDrawEmptyForeground(object sender, DevExpress.XtraGrid.Views.Base.CustomDrawEventArgs e)
        {
            if (gridView1.RowCount > 0)
                return;

            StringFormat drawFormat = new StringFormat();

            drawFormat.Alignment = drawFormat.LineAlignment = StringAlignment.Center;

            e.Graphics.DrawString("No items exist in this view", e.Appearance.Font, SystemBrushes.ControlDark, new RectangleF(e.Bounds.X, e.Bounds.Y, e.Bounds.Width, e.Bounds.Height), drawFormat);

        }

        private void gridView1_ValidateRow(object sender, DevExpress.XtraGrid.Views.Base.ValidateRowEventArgs e)
        {
            GridView view = sender as GridView;
            switch (ReportId)
            {
                case ReportConstants.ReportId.Timecard:

                    //Kiểm tra đây là dòng dữ liệu mới hay cũ, nếu là mới thì mình insert
                    if (view.IsNewItemRow(e.RowHandle))
                    {
                        //e.RowHandle trả về giá trị int là thứ tự của dòng hiện tại

                    }
                    //Cũ thì update
                    else
                    {
                        string update = "update [242_TimeCard] set Date=N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["Date"]).ToString() + "', StaffID =N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["StaffID"]).ToString() + "', [In] =N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["In"]).ToString() + "', [Out] =N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["Out"]).ToString() + "', [Time] ="
                       + view.GetRowCellValue(e.RowHandle, view.Columns["Time"]).ToString() + ", [Shift] =N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["Shift"]).ToString() + "', [Overtime] ="
                       + view.GetRowCellValue(e.RowHandle, view.Columns["Overtime"]).ToString() + ", [ABS] ="
                        + view.GetRowCellValue(e.RowHandle, view.Columns["ABS"]).ToString() + ", [Note] =N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["Note"]).ToString() + "' where [ID] ="
                       + view.GetRowCellValue(e.RowHandle, view.Columns["ID"]).ToString();
                        ReportDAO.ExecuteNonQuery(update);

                    }
                    break;

                case ReportConstants.ReportId.StaffList:

                    //Kiểm tra đây là dòng dữ liệu mới hay cũ, nếu là mới thì mình insert
                    if (view.IsNewItemRow(e.RowHandle))
                    {
                        //e.RowHandle trả về giá trị int là thứ tự của dòng hiện tại

                    }
                    //Cũ thì update
                    else
                    {
                        string update = "update [242_StaffList] set [StaffID]=N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["StaffID"]).ToString() + "', [StaffName] =N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["StaffName"]).ToString() + "', [Description] =N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["Description"]).ToString() + "', [Group1] =N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["Group1"]).ToString() + "', [Group2] =N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["Group2"]).ToString() + "', [Dept] =N'"
                       + view.GetRowCellValue(e.RowHandle, view.Columns["Dept"]).ToString() + "' where [ID] ="
                       + view.GetRowCellValue(e.RowHandle, view.Columns["ID"]).ToString();
                        ReportDAO.ExecuteNonQuery(update);

                    }
                    break;
            }
        }

        private void btDelete_Click(object sender, EventArgs e)
        {
            if (gridView1.SelectedRowsCount > 0)
            {
                var row = new DataRow[gridView1.SelectedRowsCount];
                var workNameId = new string[gridView1.SelectedRowsCount];
                string delete = "";
                switch (ReportId)
                {
                    case ReportConstants.ReportId.Timecard:
                        delete = "delete from [242_Timecard] where ID=";
                        break;
                    case ReportConstants.ReportId.StaffList:
                        delete = "delete from [242_StaffList] where ID=";
                        break;
                    default:
                        return;
                        break;
                }


                for (int i = 0; i < gridView1.SelectedRowsCount; i++)
                {
                    row[i] = gridView1.GetDataRow(gridView1.GetSelectedRows()[i]);
                    workNameId[i] = row[i]["ID"].ToString();
                }


                if (MessageBox.Show(ReportConstants.Message.DeleteQuestion, "Warning",
                                MessageBoxButtons.OKCancel, MessageBoxIcon.Question) == DialogResult.OK)
                {
                    for (int i = 0; i < workNameId.Length; i++)
                    {
                        string sql = delete + workNameId[i];
                        if (ReportDAO.ExecuteNonQuery(sql) > 0)
                        {
                            // _action = 2;
                        }
                        else
                        {
                            MessageBox.Show(ReportConstants.Message.DeleteFailed, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                    }

                    BindDataToGridview(ReportId);

                }
            }
            else
            {
                MessageBox.Show(ToolConstants.Message.TableIsNull, ToolConstants.Form.MessageBox.Message, MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
            }
        }

        private void btImport_Click(object sender, EventArgs e)
        {
            FileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "Excel 2007-2013|*.xlsx|Excel 97-2003 (*.xls)|*.xls";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                OleDbConnection connection = new OleDbConnection();
                try
                {
                    string path = fileDialog.FileName;
                    INISettings iniSettings = new INISettings();
                    string connectString = iniSettings.SQLConnectionString;
                    // Connection String to Excel Workbook
                    string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 12.0;Persist Security Info=False", path);

                    connection.ConnectionString = excelConnectionString;
                    OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);
                    connection.Open();
                    // Create DbDataReader to Data Worksheet
                    DbDataReader dr = command.ExecuteReader();

                    // Bulk Copy to SQL Server 
                    SqlBulkCopy bulkInsert = new SqlBulkCopy(connectString);
                    switch (ReportId)
                    {
                        case ReportConstants.ReportId.Timecard:
                            SqlBulkCopyColumnMapping date = new SqlBulkCopyColumnMapping("Date", "Date");
                            bulkInsert.ColumnMappings.Add(date);

                            SqlBulkCopyColumnMapping staffId = new SqlBulkCopyColumnMapping("StaffID", "StaffID");
                            bulkInsert.ColumnMappings.Add(staffId);

                            SqlBulkCopyColumnMapping cin = new SqlBulkCopyColumnMapping("In", "In");
                            bulkInsert.ColumnMappings.Add(cin);

                            SqlBulkCopyColumnMapping cout = new SqlBulkCopyColumnMapping("Out", "Out");
                            bulkInsert.ColumnMappings.Add(cout);

                            SqlBulkCopyColumnMapping note = new SqlBulkCopyColumnMapping("Note", "Note");
                            bulkInsert.ColumnMappings.Add(note);

                            SqlBulkCopyColumnMapping aBs = new SqlBulkCopyColumnMapping("ABS", "ABS");
                            bulkInsert.ColumnMappings.Add(aBs);

                            SqlBulkCopyColumnMapping Time = new SqlBulkCopyColumnMapping("Time", "Time");
                            bulkInsert.ColumnMappings.Add(Time);

                            SqlBulkCopyColumnMapping cshift = new SqlBulkCopyColumnMapping("Shift", "Shift");
                            bulkInsert.ColumnMappings.Add(cshift);

                            bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_TimeCard]";
                            bulkInsert.WriteToServer(dr);

                            break;

                        case ReportConstants.ReportId.StaffList:
                            SqlBulkCopyColumnMapping StaffID = new SqlBulkCopyColumnMapping("StaffID", "StaffID");
                            bulkInsert.ColumnMappings.Add(StaffID);

                            SqlBulkCopyColumnMapping staffName = new SqlBulkCopyColumnMapping("StaffName", "StaffName");
                            bulkInsert.ColumnMappings.Add(staffName);

                            SqlBulkCopyColumnMapping description = new SqlBulkCopyColumnMapping("Description", "Description");
                            bulkInsert.ColumnMappings.Add(description);

                            SqlBulkCopyColumnMapping group1 = new SqlBulkCopyColumnMapping("Group1", "Group1");
                            bulkInsert.ColumnMappings.Add(group1);

                            SqlBulkCopyColumnMapping group2 = new SqlBulkCopyColumnMapping("Group2", "Group2");
                            bulkInsert.ColumnMappings.Add(group2);

                            SqlBulkCopyColumnMapping dept = new SqlBulkCopyColumnMapping("Dept", "Dept");
                            bulkInsert.ColumnMappings.Add(dept);

                            bulkInsert.DestinationTableName = "[rrc_database].[dbo].[242_StaffList]";
                            bulkInsert.WriteToServer(dr);

                            break;
                    }



                    connection.Close();
                    MessageBox.Show("Import data successfully");
                    BindDataToGridview(ReportId);
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                    connection.Close();
                }

            }
        }


        
    }
}
